﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using Model;

namespace DAL
{
    public class ClassRepository
    {
        private string connectionString = "Data Source=test.db;Version=3;";

        // 添加班级
        public void AddClass(Class cls)
        {
            using (var conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                string query = "INSERT INTO Class (ClassName, SchoolID) VALUES (@name, @schoolId)";
                using (var cmd = new SQLiteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@name", cls.ClassName);
                    cmd.Parameters.AddWithValue("@schoolId", cls.SchoolID);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        // 删除班级
        public void DeleteClass(int classId)
        {
            using (var conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                string query = "DELETE FROM Class WHERE ClassID = @id";
                using (var cmd = new SQLiteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@id", classId);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        // 更新班级信息
        public void UpdateClass(Class cls)
        {
            using (var conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                string query = "UPDATE Class SET ClassName = @name, SchoolID = @schoolId WHERE ClassID = @id";
                using (var cmd = new SQLiteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@name", cls.ClassName);
                    cmd.Parameters.AddWithValue("@schoolId", cls.SchoolID);
                    cmd.Parameters.AddWithValue("@id", cls.ClassID);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        // 根据班级名称进行模糊查询
        public List<Class> SearchClassesByName(string name)
        {
            var classes = new List<Class>();
            using (var conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                string query = "SELECT * FROM Class";
                if (name != "")
                {
                    query = "SELECT * FROM Class WHERE ClassName LIKE @name";
                }
                using (var cmd = new SQLiteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@name", "%" + name + "%");
                    //逐行读取
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var classObj = new Class
                            {
                                ClassID = Convert.ToInt32(reader["ClassID"]),
                                ClassName = reader["ClassName"].ToString(),
                                SchoolID = Convert.ToInt32(reader["SchoolID"])
                            };
                            classes.Add(classObj);
                        }
                    }
                }
            }
            return classes;
        }
    }
}
